package dao.person;

import domain.Info;
import domain.Page;
import domain.Room;
import util.DButils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class PersonDaoImpl implements PersonDao{
    /**
     * 新增常住信息
     * @param community_id,room_id,name,sex,phone,idcard,identity_img1,identity_img2,photo_img,carid
     * @return int
     * @author 夏添
     * @date 2020/7/23 9:25
     */
    @Override
    public int CreatPerson(int community_id,int room_id,String name,String sex,String phone,String idcard,String identity_img1,String identity_img2,String photo_img,String carid) throws Exception{
        String sql="insert into info (Community_id,Room_id,Name,Sex,Phone,Idcard,Identity_img1,Identity_img2,photo_img,Carid,State) values("+
                community_id+","+room_id+",'"+name+"','"+sex+"','"+phone+"','"+idcard+"','"+identity_img1+"','"+identity_img2+"','"+photo_img+"','"+carid+"',0)";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 通过住房id检索该住房下的常驻人口的简单信息
     * @param room_id
     * @return info
     * @author 夏天
     * @date 2020/7/23 9:39
     */
    @Override
    public ArrayList<Info> UserPerson(int room_id) throws  Exception{
        String sql = "select Info_id,Name,Sex,State from info where Room_id="+room_id;
        ArrayList<Info> list =new ArrayList<>();
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            Info info = new Info();
            info.setInfo_id(rs.getInt("Info_id"));
            info.setName(rs.getString("Name"));
            info.setSex(rs.getString("Sex"));
            info.setState(rs.getInt("State"));
            list.add(info);
        }
        DButils.close(rs,st,con);
        return list;
    }

    /**
     * 删除住房下的常驻人口
     * @param info_id
     * @return int
     * @author 夏天
     * @date 2020/7/23 11:09
     */
    @Override
    public  int DelectPerson(int info_id) throws Exception{
        String sql = "delete from info where Info_id = "+info_id;
        Connection con =DButils.getConn();
        Statement st = con.createStatement();
        int i=st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }
    /**
     * 更新常驻人口信息
     * @param info_id,phone,idcard,identity_img1,identity_img2,photo_img,carid
     * @return int
     * @author 夏天
     * @date 2020/7/23 11:23
     */
    @Override
    public  int UpdatePerson(int info_id,String phone,String idcard,String identity_img1,String identity_img2,String photo_img,String carid)throws  Exception{
        String sql = "update info set Phone='"+phone+"',Idcard='"+idcard+"'";
        if(identity_img1!=null)
            sql += ",Identity_img1='"+identity_img1+"'";
        if(identity_img2!=null)
            sql += ",Identity_img2='"+identity_img2+"'";
        if(photo_img!=null)
            sql += ",Photo_img='"+photo_img+"'";
        sql += ",Carid='"+carid+"',State=0 where Info_id="+info_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        int i =st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 通过id获取完整信息
     * @param info_id
     * @return info
     * @author 夏天
     * @date 2020/7/23 13:40
     */
    @Override
    public Info getInfoById(int info_id) throws Exception{
        String sql ="select * from info join room on room.Room_id=info.Room_id join community on community.Community_id=info.Community_id where info.Info_id="+info_id;
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        Info info = new Info();
        while (rs.next()) {
            info.setInfo_id(rs.getInt("Info_id"));
            info.setCommunity_id(rs.getInt("Community_id"));
            info.setRoom_id(rs.getInt("Room_id"));
            info.setName(rs.getString("Name"));
            info.setSex(rs.getString("Sex"));
            info.setPhone(rs.getString("Phone"));
            info.setIdcard(rs.getString("Idcard"));
            info.setIdentity_img1(rs.getString("Identity_img1"));
            info.setIdentity_img2(rs.getString("Identity_img2"));
            info.setPhoto_img(rs.getString("Photo_img"));
            info.setCarid(rs.getString("Carid"));
            info.setState(rs.getInt("State"));
            info.setCommunity_name(rs.getString("Community_name"));
            info.setHousernum(rs.getString("Housernum"));
        }
        return info;
    }

    /**
     * 获取允许查看的住户数量
     * @param view_power
     * @return int
     * @author 夏天
     * @date 2020/7/23 14:06
     */
    @Override
    public int getCount(String view_power) throws  Exception{
        view_power = view_power.substring(0,view_power.length()-1);
        String sql = "select count(*) as count from info where Community_id in ("+view_power+")";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int i = rs.getInt("count");
        DButils.close(rs,st,con);
        return i;
    }

    /**
     * 获取符合条件的住户数量
     * @param view_power,community_id,query
     * @return int
     * @author 夏天
     * @date 2020/7/23 14:06
     */
    @Override
    public int getCount(String view_power,int community_id,String query) throws  Exception{
        String sql = null;
        if (community_id==0&&query==null) {
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select count(*) as count from info  where Community_id in ("+view_power+") " ;
        }
        if(community_id > 0 && query==null){
            sql = "select count(*) as count from info  where  Community_id="+community_id+"  " ;

        }
        if(community_id >0 && query !=null){
            sql = "select count(*) as count from info join room on room.Room_id = info.Room_id where (Name LIKE '%"+query
                    +"%' or  Housernum LIKE '%"+query+"%' or info.Phone LIKE '%"+query+"%' or Idcard LIKE '%"+query+"%' ) and info.Community_id="+community_id+"  " ;
        }
        if(community_id ==0 && query !=null){
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select count(*) as count from info join room on room.Room_id = info.Room_id where (Name LIKE '%"+query
                    +"%' or  Housernum LIKE '%"+query+"%' or info.Phone LIKE '%"+query+"%' or Idcard LIKE '%"+query+"%') and info.Community_id in ("+view_power+" )  " ;
        }
        System.out.print("查询InfoCount语句:"+sql );
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int i = rs.getInt("count");
        DButils.close(rs,st,con);
        return  i;
    }

    /**
     * 常驻人员后台检索
     * @param page,view_power
     * @return ArrayList
     * @author 夏添
     * @date 2020/7/20 20:16
     */
    @Override
    public ArrayList<Info> findByPage(Page page, String view_power) throws Exception{
        ArrayList<Info> list=new ArrayList<>();
        if (view_power==null){
            return list;
        }
        else {
            view_power = view_power.substring(0,view_power.length()-1);
            String sql = "select * from info join community on info.Community_id = community.Community_id join room on room.Room_id=info.Room_id where info.Community_id in ("+view_power+") limit " + page.getBeginIndex() + ", " + page.getEveryPage();
            Connection con = DButils.getConn();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                Info info =new Info();
                info.setInfo_id(rs.getInt("Info_id"));
                info.setCommunity_id(rs.getInt("Community_id"));
                info.setRoom_id(rs.getInt("Room_id"));
                info.setName(rs.getString("Name"));
                info.setSex(rs.getString("Sex"));
                info.setPhone(rs.getString("Phone"));
                info.setIdcard(rs.getString("Idcard"));
                info.setIdentity_img1(rs.getString("Identity_img1"));
                info.setIdentity_img2(rs.getString("Identity_img2"));
                info.setPhoto_img(rs.getString("Photo_img"));
                info.setCarid(rs.getString("Carid"));
                info.setState(rs.getInt("State"));
                info.setCommunity_name(rs.getString("Community_name"));
                info.setHousernum(rs.getString("Housernum"));
                list.add(info);
            }
            DButils.close(rs, st, con);
            return list;
        }
    }

    /**
     * 常驻人员信息搜索
     * @param page,community_id,query,view_power
     * @return ArrayList
     * @author 夏添
     * @date 2020/7/23 20:32
     */
    @Override
    public ArrayList<Info> QueryPerson(Page page,int community_id,String query,String view_power) throws  Exception{
        String sql = null;
        if (community_id==0&&query==null) {
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select * from info join community on info.Community_id = community.Community_id join room on room.Room_id=info.Room_id where info.Community_id in ("+view_power+") limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }
        if(community_id > 0 && query==null){
            sql = "select * from info join community on info.Community_id = community.Community_id join room on room.Room_id=info.Room_id where  info.Community_id="+community_id+" limit " + page.getBeginIndex() + ", " + page.getEveryPage();

        }
        if(community_id >0 && query !=null){
            sql = "select * from info join community on info.Community_id = community.Community_id join room on room.Room_id=info.Room_id where (Name LIKE '%"+query
                    +"%' or  Housernum LIKE '%"+query+"%' or info.Phone LIKE '%"+query+"%' or Idcard LIKE '%"+query+"%' ) and info.Community_id="+community_id+" limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }
        if(community_id ==0 && query !=null){
            view_power = view_power.substring(0,view_power.length()-1);
            sql = "select * from info join community on info.Community_id = community.Community_id join room on room.Room_id=info.Room_id where (Name LIKE '%"+query
                    +"%' or  Housernum LIKE '%"+query+"%' or info.Phone LIKE '%"+query+"%' or Idcard LIKE '%"+query+"%') and info.Community_id in ("+view_power+" )  limit " + page.getBeginIndex() + ", " + page.getEveryPage();
        }
        Connection con = DButils.getConn();
        ArrayList<Info> list = new ArrayList<>();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            Info info =new Info();
            info.setInfo_id(rs.getInt("Info_id"));
            info.setCommunity_id(rs.getInt("Community_id"));
            info.setRoom_id(rs.getInt("Room_id"));
            info.setName(rs.getString("Name"));
            info.setSex(rs.getString("Sex"));
            info.setPhone(rs.getString("Phone"));
            info.setIdcard(rs.getString("Idcard"));
            info.setIdentity_img1(rs.getString("Identity_img1"));
            info.setIdentity_img2(rs.getString("Identity_img2"));
            info.setPhoto_img(rs.getString("Photo_img"));
            info.setCarid(rs.getString("Carid"));
            info.setState(rs.getInt("State"));
            info.setCommunity_name(rs.getString("Community_id"));
            info.setHousernum(rs.getString("Housernum"));
            list.add(info);
        }
        DButils.close(rs, st, con);
        return list;
    }

    /**
     * 审核通过
     * @param info_id
     * @return int
     * @author 夏天
     * @date 2020/7/23 15:57
     */
    @Override
    public int StateYes(int info_id) throws Exception{
        String sql = "update info set State=1 where Info_id="+info_id;
        Connection con =DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }

    /**
     * 审核不通过
     * @param info_id
     * @retrun int
     * @author 夏天
     * @date 2020/7/23 15:58
     */
    @Override
    public int StateNo(int info_id) throws Exception{
        String sql = "update info set State=2 where Info_id="+info_id;
        Connection con =DButils.getConn();
        Statement st = con.createStatement();
        int i = st.executeUpdate(sql);
        DButils.close(st,con);
        return i;
    }
}
